﻿Imports MySql.Data.MySqlClient

Public Class checkout
    Dim connString As String = "Server=localhost; User Id=root; Password=; Database=leximoore"

    Public Sub checkout()
        Static Dim roomType As Integer = 0
        Dim code As String = checkOutForm.bookingCodeTxt.Text()
        Dim dr As MySqlDataReader
        Dim count As Integer = 0
        Dim room_id As Integer = 0


        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "SELECT COUNT(*) FROM reservation WHERE confirmation=@code"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@code", code)
                End With
                Try
                    SQLConnection.Open()
                    dr = sqlCommand.ExecuteReader()
                    While (dr.Read())
                        count = dr.GetString(0)
                    End While
                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using


        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "SELECT room_id FROM reservation WHERE confirmation=@code"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@code", code)
                End With
                Try
                    SQLConnection.Open()
                    dr = sqlCommand.ExecuteReader()
                    While (dr.Read())
                        room_id = dr.GetString(0)
                    End While
                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using


        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "UPDATE room SET qty = qty + 1 WHERE room_id=@room_id"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@room_id", room_id)
                End With
                Try
                    SQLConnection.Open()
                    Dim i As Integer
                    For i = 0 To count - 1 Step 1
                        sqlCommand.ExecuteNonQuery()
                    Next i
                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using

        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "DELETE FROM reservation WHERE confirmation=@code"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@code", code)
                End With
                Try
                    SQLConnection.Open()
                    sqlCommand.ExecuteNonQuery()
                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using
        Using SQLConnection As New MySqlConnection(connString)
            Using sqlCommand As New MySqlCommand()
                With sqlCommand
                    .CommandText = "DELETE FROM roominventory WHERE confirmation=@code"
                    .Connection = SQLConnection
                    .CommandType = CommandType.Text
                    .Parameters.AddWithValue("@code", code)
                End With
                Try
                    SQLConnection.Open()
                    sqlCommand.ExecuteNonQuery()
                Catch ex As MySqlException
                    Console.WriteLine(ex)
                    Console.ReadLine()
                Finally
                    SQLConnection.Close()
                End Try
            End Using
        End Using


    End Sub

End Class
